package com.lsh.streamgd.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hp.hpl.jena.rdf.model.Model;
import com.hp.hpl.jena.util.FileManager;
import com.lsh.streamgd.beans.CsvUploadInfo;
import com.lsh.streamgd.beans.TableColumn;
import com.lsh.streamgd.beans.TaskConfig;
import com.lsh.streamgd.beans.WhiteNameColums;
import com.lsh.streamgd.mappers.Generate_mapping;
import com.lsh.streamgd.sparql.OWLParserSparql;
import com.lsh.streamgd.sparql.Upload;
import com.lsh.streamgd.sparql.impl.OWLParserSparqlImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.*;

/**
 * Created by ljgsonx on 2017/1/14.
 */
public class TaskUtil {

    private static Logger logger = LoggerFactory.getLogger("TaskUtil");

    private static List<Map<String, String>> getAllTablesFromTTL(String companyName, String owlTTLFilePath) {
        List<Map<String, String>> resultList = new ArrayList<>();
        if (!StringUtil.hasBlank(companyName, owlTTLFilePath)) {
            //解析TTL文件，原始Model
            Model assemblerSpec = FileManager.get().loadModel(owlTTLFilePath, Settings.graphDomain + Constants.PREFIX_ONTOLOGY + companyName + "/", "TTL");
            OWLParserSparql owl = new OWLParserSparqlImpl(assemblerSpec);
            resultList = owl.getAllTables();
            logger.info("\n=====>Get the opened Tables: {}", StringUtil.getJSONDefault(resultList));
        }
        return resultList;
    }

    private static List<Map<String, String>> getAllColumnsFromTTLByTableUri(String companyName, String owlTTLFilePath, String tableUri) {
        List<Map<String, String>> resultList = new ArrayList<>();
        if (!StringUtil.hasBlank(companyName, owlTTLFilePath, tableUri)) {
            //解析TTL文件，原始Model
            Model assemblerSpec = FileManager.get().loadModel(owlTTLFilePath, Settings.graphDomain + Constants.PREFIX_ONTOLOGY + companyName + "/", "TTL");
            OWLParserSparql owl = new OWLParserSparqlImpl(assemblerSpec);
            resultList = owl.getColumns(tableUri);
            logger.info("\n=====>Get the opened Columns: {}", StringUtil.getJSONDefault(resultList));
        }
        return resultList;
    }

    public static Map<String[], List<String[]>> setWhiteNameList(TaskConfig taskConfig, boolean executedByTask) {
        if (taskConfig == null) return null;
        if (StringUtil.isBlank(taskConfig.getId())) {
            //第一次新建任务
            taskConfig.setId(UUID.randomUUID().toString().replace("-", ""));
        }
        List<WhiteNameColums> whiteNameColumsList = new ArrayList<>();
        JSONArray ja = JSONArray.parseArray(taskConfig.getWhiteNameColumns());
        for (int i = 0; i < ja.size(); i++) {
            WhiteNameColums wnc = new WhiteNameColums();
            wnc.setTableName(ja.getJSONObject(i).getString("tableName"));
            wnc.setUpdateColumn(ja.getJSONObject(i).getString("updateColumn"));
            JSONArray ja_columns = ja.getJSONObject(i).getJSONArray("tableColoumns");
            List<TableColumn> tableColumnsList = new ArrayList<>();
            for (int j = 0; j < ja_columns.size(); j++) {
                TableColumn tc = ja_columns.getObject(j, TableColumn.class);
                tableColumnsList.add(tc);
            }
            wnc.setTableColumns(tableColumnsList);
            whiteNameColumsList.add(wnc);
        }
        StringBuilder columns = new StringBuilder();
        for (int i = 0; i < whiteNameColumsList.size(); i++) {
            WhiteNameColums wnc = whiteNameColumsList.get(i);
            for (int j = 0; j < wnc.getTableColumns().size(); j++) {
                columns.append(wnc.getTableName()).append(".")
                        .append(wnc.getTableColumns().get(j).getColumnName())
                        .append(",");
            }
        }
        if (columns.length() > 0) {
            columns.deleteCharAt(columns.length() - 1);
        }
        File ttlFolder = new File(taskConfig.getRootPath() + "ttls\\" + taskConfig.getId());
        if (!ttlFolder.exists()) {
            ttlFolder.mkdirs();
        }

        String owlTTLFilePath = ttlFolder.getPath() + "\\result-owl.ttl";

        //写入限制条件
        if(!executedByTask){
            String settings[] = new String[9];
            settings[0] = "-u";
            settings[1] = taskConfig.getUserName();
            settings[2] = "-p";
            settings[3] = taskConfig.getUserPwd();
            settings[4] = "-o";
            settings[5] = ttlFolder.getPath() + "\\result.ttl";
            settings[6] = "--columns";
            settings[7] = columns.toString();
            if (taskConfig.getDbType() == Constants.DBType.MYSQL) {
                settings[8] = "jdbc:mysql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
            } else if (taskConfig.getDbType() == Constants.DBType.POSTGRES) {
                settings[8] = "jdbc:postgresql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
            } else if (taskConfig.getDbType() == Constants.DBType.ORACLE) {
                if (taskConfig.getDbConnectType() == Constants.DBConnectType.SID) {
                    settings[8] = "jdbc:oracle:thin:@" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + ":" + taskConfig.getDbName();
                } else {
                    settings[8] = "jdbc:oracle:thin:@//" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
                }
            } else {
                logger.error("\n=====[dbInfo type error ]=====");
                return null;
            }
            new Generate_mapping().process(settings, Settings.graphDomain + Constants.PREFIX_ONTOLOGY + taskConfig.getCompanyName() + "/");
            //生成owl文件
            String settings_owl[] = new String[10];
            for (int i = 0; i < settings.length - 1; i++) {
                if (i == 5) {
                    settings_owl[i] = owlTTLFilePath;
                } else {
                    settings_owl[i] = settings[i];
                }
            }
            settings_owl[8] = "-v";
            if (taskConfig.getDbType() == Constants.DBType.MYSQL) {
                settings_owl[9] = "jdbc:mysql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
            } else if (taskConfig.getDbType() == Constants.DBType.POSTGRES) {
                settings_owl[9] = "jdbc:postgresql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
            } else if (taskConfig.getDbType() == Constants.DBType.ORACLE) {
                if (taskConfig.getDbConnectType() == Constants.DBConnectType.SID) {
                    settings_owl[9] = "jdbc:oracle:thin:@" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + ":" + taskConfig.getDbName();
                } else {
                    settings_owl[9] = "jdbc:oracle:thin:@//" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
                }
            } else {
                logger.error("\n=====[dbInfo type error ]=====");
                return null;
            }

            new Generate_mapping().process(settings_owl, Settings.graphDomain + Constants.PREFIX_ONTOLOGY + taskConfig.getCompanyName() + "/");
            Model mappingModel = FileManager.get().loadModel(ttlFolder.getPath() + "\\result.ttl");
            OWLParserSparql owlParserSparql = new OWLParserSparqlImpl(mappingModel);
            for (WhiteNameColums wnc : whiteNameColumsList) {
                String tableName = wnc.getTableName();
                String tableNameUpper = tableName.replaceFirst(tableName.substring(0, 1), tableName.substring(0, 1).toUpperCase());

                //加入用户输入的过滤条件
                if (!StringUtil.isBlank(tableName) && tableName.length() > 0) {
                    List<TableColumn> tcs = wnc.getTableColumns();
                    for (TableColumn tc : tcs) {
                        if (!StringUtil.isBlank(tc.getFilterCondition())) {
                            String condition = tableName + "." + tc.getColumnName() + " " + tc.getFilterCondition().trim();
                            logger.info("\n=====[  insert column filer  ]=====\n\t{}", condition);
                            owlParserSparql.insertFilter(mappingModel.getNsPrefixURI("map") + tableNameUpper, "d2rq:condition", condition);
                        }
                    }
                }
                //加入增量更新的过滤条件
//                String updateColumnName = wnc.getUpdateColumn();
//                if (!StringUtil.isBlank(updateColumnName)) {
//                    String maxUpdateColumnValue = taskConfig.getMaxUpdateColumnValue();
//                    if (!StringUtil.isBlank(maxUpdateColumnValue)) {
//                        JSONObject mucv = JSONObject.parseObject(maxUpdateColumnValue);
//                        if (mucv != null && !mucv.isEmpty()) {
//                            String ele_maxUpdateColumnValue = mucv.getString(tableName);
//                            if (!StringUtil.isBlank(ele_maxUpdateColumnValue)) {
//                                String condition = tableName + "." + wnc.getUpdateColumn() + " > " + ele_maxUpdateColumnValue;
//                                logger.info("\n=====[  insert update column filer  ]=====\n\t{}", condition);
//                                owlParserSparql.insertFilter(mappingModel.getNsPrefixURI("map") + tableNameUpper, "d2rq:condition", condition);
//                            }
//                        }
//                    }
//                }
            }
            //重写result.ttl文件
            ByteArrayOutputStream stream = new ByteArrayOutputStream();
            mappingModel.write(stream, "TTL");
            File newMappingFile = new File(ttlFolder.getPath() + "\\result.ttl");
            try {
                FileOutputStream fos = new FileOutputStream(newMappingFile);
                try {
                    fos.write(stream.toByteArray());
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
        }

        //读取ttl文件,同时获取生成uri
        String companyName = taskConfig.getCompanyName();
        Map<String[], List<String[]>> openedTableColumn = new HashMap<>();
        List<Map<String, String>> openedTables = getAllTablesFromTTL(companyName, owlTTLFilePath);
        for (Map opendTable : openedTables) {
            String[] tableInfo = new String[2];
            for (WhiteNameColums wnc : whiteNameColumsList) {
                if (!StringUtil.isBlank(wnc.getTableName()) && wnc.getTableName().equals(opendTable.get("tableName"))) {
                    tableInfo[0] = wnc.getTableName();
                    tableInfo[1] = (String) opendTable.get("tableUri");
                    List<Map<String, String>> openedColumns = getAllColumnsFromTTLByTableUri(companyName, owlTTLFilePath, tableInfo[1]);
                    List<String[]> openedColumns2 = new ArrayList<>();
                    for (Map<String, String> openedColumn : openedColumns) {
                        List<TableColumn> tcs = wnc.getTableColumns();
                        boolean hasColumn = false;
                        for (TableColumn tc : tcs) {
                            if (!StringUtil.isBlank(tc.getColumnName())) {
                                String columnName = tableInfo[0] + "_" + tc.getColumnName();
                                if (columnName.equals(openedColumn.get("columnName"))) {
                                    String[] columnInfo = new String[3];
                                    columnInfo[0] = tc.getColumnName();
                                    columnInfo[1] = tc.getColumnComment();
                                    columnInfo[2] = openedColumn.get("columnUri");
                                    openedColumns2.add(columnInfo);
                                    hasColumn = true;
                                    break;
                                }
                            }
                        }
                        if (!hasColumn) {
                            String[] columnInfoNew = new String[3];
                            columnInfoNew[0] = openedColumn.get("columnName");
                            columnInfoNew[1] = "";
                            columnInfoNew[2] = openedColumn.get("columnUri");
                            openedColumns2.add(columnInfoNew);
                        }
                    }
                    openedTableColumn.put(tableInfo, openedColumns2);
                    break;
                }
            }
        }
        if (openedTableColumn.size() > 0) {
            return openedTableColumn;
        }
        return null;
    }

    public static boolean insertComments(TaskConfig taskConfig) {
        if(taskConfig == null) return false;
        File ttlFolder = new File(taskConfig.getRootPath() + "ttls\\" + taskConfig.getId());
        if (!ttlFolder.exists()) {
            ttlFolder.mkdirs();
        }
        String owlTTLFilePath = ttlFolder.getPath() + "\\result-owl.ttl";
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        String companyName = taskConfig.getCompanyName();
        //解析TTL文件，原始Model
        Model assemblerSpec = FileManager.get().loadModel(owlTTLFilePath, Settings.graphDomain + Constants.PREFIX_ONTOLOGY + companyName + "/", "TTL");
        OWLParserSparql owl = new OWLParserSparqlImpl(assemblerSpec);
        JSONArray uri_ja = JSONArray.parseArray(taskConfig.getUris());
        JSONArray comment_ja = JSONArray.parseArray(taskConfig.getComments());
        for (int i = 0; i < uri_ja.size(); i++) {
            String comment = comment_ja.getString(i);
            if (!StringUtil.isBlank(comment)) {
                owl.insertComment(uri_ja.getString(i), comment);
            }
        }
        assemblerSpec.write(stream, "TTL");
        File newTTLfile = new File(ttlFolder.getPath() + "\\result-owl-comment.ttl");
        if (!newTTLfile.exists()) {
            try {
                newTTLfile.createNewFile();
            } catch (IOException e) {
                logger.error("\n=====failed to create new ttl file=====");
                e.printStackTrace();
            }
        }
        try {
            FileOutputStream fos = new FileOutputStream(newTTLfile);
            try {
                fos.write(stream.toByteArray());
                return true;
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean upLoad(TaskConfig taskConfig, boolean executedByTask) {
        try {
            File ttlFolder = new File(taskConfig.getRootPath() + "ttls\\" + taskConfig.getId());
            if (!ttlFolder.exists()) {
                ttlFolder.mkdirs();
            }
            String owlTTLFilePath = ttlFolder.getPath() + "\\result.ttl";
            //增量，直接更新；全量，需要删除已有数据
            if (taskConfig.isBackTrackable()) {
                Upload upload = new Upload(Settings.graphDriver, Settings.graphHost, Settings.graphUsername, Settings.graphPassword);
                String companyName = taskConfig.getCompanyName();
                Model owlModel = FileManager.get().loadModel(ttlFolder.getPath() + "\\result-owl.ttl");
                //获取回溯字段
                OWLParserSparql owlParserSparql = new OWLParserSparqlImpl(owlModel);
                List<Map<String, String>> traces = owlParserSparql.getTraceColumns();

                //远程删除virtuoso上数据
                for (int i = 0; i < traces.size(); i++) {
                    String c = ((Map) traces.get(i)).get("class").toString();
                    String p = ((Map) traces.get(i)).get("property").toString();
                    upload.trace2RDF(c, p);
                }

                //重新导入数据
                upload.convert2RDF(owlTTLFilePath, Settings.graphDomain + Constants.PREFIX_RESOURCE + companyName + "/");
                return true;
            } else {
                if (new File(owlTTLFilePath).exists()) {
                    String companyName = taskConfig.getCompanyName();
                    if (!StringUtil.isBlank(companyName)) {
                        Upload upload = new Upload(Settings.graphDriver, Settings.graphHost, Settings.graphUsername, Settings.graphPassword);
                        upload.convert2RDF(owlTTLFilePath, Settings.graphDomain + Constants.PREFIX_RESOURCE + companyName + "/");
                        if (!executedByTask) {
                            //不远程传送文件，直接读取内容后存储到数据库
                            upload.convert2RDF(ttlFolder.getPath() + "\\result-owl-comment.ttl");
                        }
                        //获取每张表的增量更新列的最大值给taskConfig
                        DBConnecter.getMaxColumnValue(taskConfig);

                        JSONArray ja = JSONArray.parseArray(taskConfig.getWhiteNameColumns());
                        List<String> conditions = new ArrayList<>();
                        for (int i = 0; i < ja.size(); i++) {
                            String tableName = ja.getJSONObject(i).getString("tableName");
//                        String tableNameUpper = tableName.replaceFirst(tableName.substring(0, 1), tableName.substring(0, 1).toUpperCase());
                            String updateColumn = ja.getJSONObject(i).getString("updateColumn");
                            String maxUpdateColumnValue = taskConfig.getMaxUpdateColumnValue();
                            if (!StringUtil.hasBlank(updateColumn, maxUpdateColumnValue)) {
                                JSONObject mucv = JSONObject.parseObject(maxUpdateColumnValue);
                                if (mucv != null && !mucv.isEmpty()) {
                                    String ele_maxUpdateColumnValue = mucv.getString(tableName);
                                    if (!StringUtil.isBlank(ele_maxUpdateColumnValue)) {
                                        String condition = tableName + "." + updateColumn + " > " + ele_maxUpdateColumnValue;
                                        conditions.add(condition);
                                    }
                                }
                            }
                        }

                        //重写更新过滤条件
                        //写入限制条件
                        Model mappingModel = FileManager.get().loadModel(owlTTLFilePath);
                        OWLParserSparql owlParserSparql = new OWLParserSparqlImpl(mappingModel);

                        for (int i = 0; i < conditions.size(); i++) {
                            String condition = conditions.get(i);
                            String str = condition.split("\\.")[0];
                            str = str.replaceFirst(str.substring(0, 1), str.substring(0, 1).toUpperCase());
                            owlParserSparql.insertFilter(mappingModel.getNsPrefixURI("map") + str, "d2rq:condition", condition);
                        }

                        //重写result.ttl文件
                        ByteArrayOutputStream stream = new ByteArrayOutputStream();
                        mappingModel.write(stream, "TTL");
                        File newMappingFile = new File(owlTTLFilePath);
                        try {
                            FileOutputStream fos = new FileOutputStream(newMappingFile);
                            try {
                                fos.write(stream.toByteArray());
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                        } catch (FileNotFoundException e) {
                            e.printStackTrace();
                        }

                        return true;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean csvUpload(CsvUploadInfo csvUploadInfo) {


        return false;
    }
}
